package com.jing21.learn.codegenerator;

import com.jing21.learn.codegenerator.model.Column;
import com.jing21.learn.codegenerator.model.ForeignKey;
import com.jing21.learn.codegenerator.model.Table;
import com.jing21.learn.codegenerator.util.Cache;
import com.jing21.learn.codegenerator.util.ListHashtable;
import com.jing21.learn.codegenerator.util.Utility;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.sql.*;
import java.util.*;


/**
 * @author zhengjing
 */
public class DbModelProvider {
    /**
     * Logger for this class
     */
    private static final Log _log = LogFactory.getLog(Generator.class);

    //	Properties props;
    public String catalog;
    public String schema;

    private Connection connection;
    private static DbModelProvider instance = new DbModelProvider();

    private DbModelProvider() {
        init();
    }

    private void init() {

        this.schema = PropertiesProvider.getProperty("jdbc.schema", "");
        if ("".equals(schema.trim())) {
            this.schema = null;
        }
        this.catalog = PropertiesProvider.getProperty("jdbc.catalog", "");
        if ("".equals(catalog.trim())) {
            this.catalog = null;
        }

        System.out.println("jdbc.schema=" + this.schema + " jdbc.catalog=" + this.catalog);
        try {
            Class.forName(PropertiesProvider.getProperty("jdbc.driver"));
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public static DbModelProvider getInstance() {
        return instance;
    }

    private Connection getConnection() throws SQLException {
        if (connection == null || connection.isClosed()) {
            connection = DriverManager.getConnection(PropertiesProvider.getProperty("jdbc.url"), PropertiesProvider
                    .getProperty("jdbc.username"), PropertiesProvider.getProperty("jdbc.password"));
        }
        return connection;
    }

    private Connection getConnection(String url, String userName, String password) throws SQLException {
        if (connection == null || connection.isClosed()) {
            connection = DriverManager.getConnection(url, userName, password);
        }
        return connection;
    }

    private Connection getConnection(String dbname) throws SQLException {
        if (connection == null || connection.isClosed()) {
            String template = PropertiesProvider.getProperty("jdbc.url.template");
            Map<String, String> map = new HashMap<>();
            map.put("dbname", dbname);
            String uri = Utility.strFormatUsingDict(template, map);
            System.err.println("uri：" + uri);
            connection = DriverManager.getConnection(uri, PropertiesProvider
                    .getProperty("jdbc.username"), PropertiesProvider.getProperty("jdbc.password"));
        }
        return connection;
    }


    public List<Table> getAllTables() throws Exception {
        Connection conn = getConnection();
        return getAllTables(conn);
    }

    public Table getTable(String sqlTableName) throws Exception {
        Connection conn = getConnection();
        DatabaseMetaData dbMetaData = conn.getMetaData();
        ResultSet rs = dbMetaData.getTables(catalog, schema, sqlTableName, null);
        while (rs.next()) {
            Table table = createTable(conn, rs);
            return table;
        }
        throw new RuntimeException("not found table with give name:" + sqlTableName);
    }

    /**
     * 初始化表
     *
     * @param conn
     * @param rs
     * @return
     * @throws SQLException
     */
    private Table createTable(Connection conn, ResultSet rs) throws SQLException {
        ResultSetMetaData rsMetaData = rs.getMetaData();
        String schemaName = rs.getString("TABLE_SCHEM") == null ? "" : rs.getString("TABLE_SCHEM");
        String realTableName = rs.getString("TABLE_NAME");
        String tableType = rs.getString("TABLE_TYPE");
        String tableRemark = rs.getString("REMARKS");//表注释
        Table table = new Table();
        table.setSqlName(realTableName);
        table.setTableRemark(tableRemark);
        if ("SYNONYM".equals(tableType) && isOracleDataBase()) {
            table.setOwnerSynonymName(getSynonymOwner(realTableName));
        }


        //初始化外键对象
        table.initExportedKeys(conn.getMetaData());
        table.initImportedKeys(conn.getMetaData());

        //初始化字段对象
        retriveTableColumns(table);
        return table;
    }

    /**
     * 初始化字段对象准备
     *
     * @param table
     * @throws SQLException
     */
    private void retriveTableColumns(Table table) throws SQLException {
        _log.debug("-------setColumns(" + table.getSqlName() + ")");

        List primaryKeys = getTablePrimaryKeys(table);
        table.setPrimaryKeyColumns(primaryKeys);

        // get the indices and unique columns
        List indices = new LinkedList();
        // maps index names to a list of columns in the index
        Map uniqueIndices = new HashMap();
        // maps column names to the index name.
        Map uniqueColumns = new HashMap();
        ResultSet indexRs = null;

        try {

            if (table.getOwnerSynonymName() != null) {
                indexRs = getMetaData().getIndexInfo(catalog, table.getOwnerSynonymName(), table.getSqlName(), false,
                        true);
            } else {
                indexRs = getMetaData().getIndexInfo(catalog, schema, table.getSqlName(), false, true);
            }
            while (indexRs.next()) {
                String columnName = indexRs.getString("COLUMN_NAME");
                if (columnName != null) {
                    _log.debug("index:" + columnName);
                    indices.add(columnName);
                }

                // now look for unique columns
                String indexName = indexRs.getString("INDEX_NAME");
                boolean nonUnique = indexRs.getBoolean("NON_UNIQUE");

                if (!nonUnique && columnName != null && indexName != null) {
                    List l = (List) uniqueColumns.get(indexName);
                    if (l == null) {
                        l = new ArrayList();
                        uniqueColumns.put(indexName, l);
                    }
                    l.add(columnName);
                    uniqueIndices.put(columnName, indexName);
                    _log.debug("unique:" + columnName + " (" + indexName + ")");
                }
            }
        } catch (Throwable t) {
            // Bug #604761 Oracle getIndexInfo() needs major grants
            // http://sourceforge.net/tracker/index.php?func=detail&aid=604761&group_id=36044&atid=415990
            t.printStackTrace();
        } finally {
            if (indexRs != null) {
                indexRs.close();
            }
        }

        /**
         *
         */
        List columns = getTableColumns(table, primaryKeys, indices, uniqueIndices, uniqueColumns);

        table.setColumns(columns);

        // In case none of the columns were primary keys, issue a warning.
        if (primaryKeys.size() == 0) {
            _log.warn("WARNING: The JDBC driver didn't report any primary key columns in " + table.getSqlName());
        }
    }

    /**
     * 初始化字段对象
     *
     * @param table
     * @throws SQLException
     */
    private List getTableColumns(Table table, List primaryKeys, List indices, Map uniqueIndices, Map uniqueColumns)
            throws SQLException {
        // get the columns
        List columns = new LinkedList();
        ResultSet columnRs = getColumnsResultSet(table);
        int i = 0;
        Map fkMap = new HashMap();

        ListHashtable fklist = table.getImportedKeys().getAssociatedTables();
        List fkList = fklist.getOrderedValues();
        for (int t = 0; t < fkList.size(); t++) {
            ForeignKey fk = (ForeignKey) fkList.get(t);
            List fktable = fk.getParentColumns().getOrderedValues();
            for (int l = 0; l < fktable.size(); l++) {
                fkMap.put(fktable.get(l), "fk");
            }
        }

        String nogenerate = PropertiesProvider.props.getProperty("nogenerate");
        while (columnRs.next()) {
            int sqlType = columnRs.getInt("DATA_TYPE");
            String sqlTypeName = columnRs.getString("TYPE_NAME");

            String columnName = columnRs.getString("COLUMN_NAME");
            boolean isfk = false;
            if ("fk".equals(fkMap.get(columnName))) {
                isfk = true;
            }
            //ylf 除掉不用生成的字段
            if (nogenerate != null && nogenerate.indexOf(columnName) > -1) continue;

            String columnDefaultValue = columnRs.getString("COLUMN_DEF");
            // if columnNoNulls or columnNullableUnknown assume "not nullable"
            boolean isNullable = (DatabaseMetaData.columnNullable == columnRs.getInt("NULLABLE"));
            int size = columnRs.getInt("COLUMN_SIZE");
            int decimalDigits = columnRs.getInt("DECIMAL_DIGITS");

            String remarks = columnRs.getString("REMARKS");

            //remarks=(String)java.net.URLEncoder.encode(remarks, "UTF-8");


            boolean isPk = primaryKeys.contains(columnName);
            boolean isIndexed = indices.contains(columnName);
            String uniqueIndex = (String) uniqueIndices.get(columnName);
            List columnsInUniqueIndex = null;
            if (uniqueIndex != null) {
                columnsInUniqueIndex = (List) uniqueColumns.get(uniqueIndex);
            }

            boolean isUnique = columnsInUniqueIndex != null && columnsInUniqueIndex.size() == 1;
            if (isUnique) {
                _log.debug("unique column:" + columnName);
            }
            Column column = new Column(
                    table,
                    sqlType,
                    sqlTypeName,
                    columnName,
                    size,
                    decimalDigits,
                    isPk,
                    isNullable,
                    isIndexed,
                    isUnique,
                    columnDefaultValue, remarks, isfk);
            if (!columns.contains(column)) {
                columns.add(column);
            }
            i++;
        }
        columnRs.close();
        return columns;
    }

    private boolean isOracleDataBase() {
        boolean ret = false;
        try {
            ret = (getMetaData().getDatabaseProductName().toLowerCase()
                    .indexOf("oracle") != -1);
        } catch (Exception ignore) {
        }
        return ret;
    }

    private String getSynonymOwner(String synonymName) {
        PreparedStatement ps = null;
        ResultSet rs = null;
        String ret = null;
        try {
            ps = getConnection().prepareStatement("select table_owner from sys.all_synonyms where table_name=? and " +
                    "owner=?");
            ps.setString(1, synonymName);
            ps.setString(2, schema);
            rs = ps.executeQuery();
            if (rs.next()) {
                ret = rs.getString(1);
            } else {
                String databaseStructure = getDatabaseStructureInfo();
                throw new RuntimeException("Wow! Synonym " + synonymName + " not found. How can it happen? " +
                        databaseStructure);
            }
        } catch (SQLException e) {
            String databaseStructure = getDatabaseStructureInfo();
            _log.error(e.getMessage(), e);
            throw new RuntimeException("Exception in getting synonym owner " + databaseStructure);
        } finally {
            if (rs != null) {
                try {
                    rs.close();
                } catch (Exception e) {
                }
            }
            if (ps != null) {
                try {
                    ps.close();
                } catch (Exception e) {
                }
            }
        }
        return ret;
    }

    private String getDatabaseStructureInfo() {
        ResultSet schemaRs = null;
        ResultSet catalogRs = null;
        String nl = System.getProperty("line.separator");
        StringBuffer sb = new StringBuffer(nl);
        // Let's give the user some feedback. The exception
        // is probably related to incorrect schema configuration.
        sb.append("Configured schema:").append(schema).append(nl);
        sb.append("Configured catalog:").append(catalog).append(nl);

        try {
            schemaRs = getMetaData().getSchemas();
            sb.append("Available schemas:").append(nl);
            while (schemaRs.next()) {
                sb.append("  ").append(schemaRs.getString("TABLE_SCHEM")).append(nl);
            }
        } catch (SQLException e2) {
            _log.warn("Couldn't get schemas", e2);
            sb.append("  ?? Couldn't get schemas ??").append(nl);
        } finally {
            try {
                schemaRs.close();
            } catch (Exception ignore) {
            }
        }

        try {
            catalogRs = getMetaData().getCatalogs();
            sb.append("Available catalogs:").append(nl);
            while (catalogRs.next()) {
                sb.append("  ").append(catalogRs.getString("TABLE_CAT")).append(nl);
            }
        } catch (SQLException e2) {
            _log.warn("Couldn't get catalogs", e2);
            sb.append("  ?? Couldn't get catalogs ??").append(nl);
        } finally {
            try {
                catalogRs.close();
            } catch (Exception ignore) {
            }
        }
        return sb.toString();
    }

    private DatabaseMetaData getMetaData() throws SQLException {
        return getConnection().getMetaData();
    }


    private ResultSet getColumnsResultSet(Table table) throws SQLException {
        ResultSet columnRs = null;
        if (table.getOwnerSynonymName() != null) {
            columnRs = getMetaData().getColumns(catalog, table.getOwnerSynonymName(), table.getSqlName(), null);
        } else {
            columnRs = getMetaData().getColumns(catalog, schema, table.getSqlName(), null);
        }
        return columnRs;
    }

    private List getTablePrimaryKeys(Table table) throws SQLException {
        // get the primary keys
        List primaryKeys = new LinkedList();
        ResultSet primaryKeyRs = null;
        if (table.getOwnerSynonymName() != null) {
            primaryKeyRs = getMetaData().getPrimaryKeys(catalog, table.getOwnerSynonymName(), table.getSqlName());
        } else {
            primaryKeyRs = getMetaData().getPrimaryKeys(catalog, schema, table.getSqlName());
        }
        while (primaryKeyRs.next()) {
            String columnName = primaryKeyRs.getString("COLUMN_NAME");
            _log.debug("primary key:" + columnName);
            primaryKeys.add(columnName);
        }
        primaryKeyRs.close();
        return primaryKeys;
    }


    public List<Table> getAllTables(String dbname) throws SQLException {
        List<Table> cache = Cache.getCache(dbname);
        if (cache != null)
            return cache;
        List<Table> allTables = getAllTables(getConnection(dbname));
        Cache.cache(dbname, allTables);
        return allTables;
    }

    public List<Table> getAllTables(Connection conn) throws SQLException {

        List<Table> tables = new ArrayList();
        try {
            DatabaseMetaData dbMetaData = conn.getMetaData();
            ResultSet rs = dbMetaData.getTables(catalog, schema, null, null);
            while (rs.next()) {
                Table table = createTable(conn, rs);
                tables.add(table);
            }
        } finally {
            conn.close();
        }

        return tables;
    }

}
